package org.languageguide.readings;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.languageguide.storage.DBConnector;
import org.languageguide.user.User;

public class ReadingManager {
	
	public static TranslatedReading getReading(String id, String targetLanguage){
		TranslatedReading reading = new TranslatedReadingImpl(id, targetLanguage);
		return reading;
	}

	/*public static void setStatus(String id, String status){
		String sql = "Update Readings Set Status=\"" + status + "\" WHERE Id=" + id;
		
	}*/
	
	public static int saveTimes(String id, String times, String adjustedWordIds, User user){
		return saveTimes(id, times, adjustedWordIds, null, user);
	}
		
	public static int saveTimes(String id, String times, String adjustedWordIds, String status, User user){
		String adjustedWordIdInsertionClause;
		//String highestIdProcessedInsertionClause;
		if (adjustedWordIds != null){
			adjustedWordIdInsertionClause = ", AdjustedWordIds=\"" + adjustedWordIds + "\"";
		}
		else adjustedWordIdInsertionClause = "";
		
		/*if (highestIdProcessed != null){
			highestIdProcessedInsertionClause = ", HighestIdProcessed=" + highestIdProcessed;
		}
		else highestIdProcessedInsertionClause = "";*/
		
		Integer userId = user.getIdInteger();
		String sql;
		if (status != null){
			sql = "Update Readings Set Times=\"" + times + "\"" + adjustedWordIdInsertionClause +  " , Status=\"" + status + "\", UserId=\"" + userId + "\" WHERE Id=" + id;
		}
		else{
			sql = "Update Readings Set Times=\"" + times + "\"" + adjustedWordIdInsertionClause + ", UserId=\"" + userId + "\" WHERE Id=" + id;			
		}
		//System.out.println("sql" + sql);
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try{
			Statement statement = connection.createStatement();
			return statement.executeUpdate(sql);
		}
		catch (SQLException e){
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
		return 0;	
		
	}
	
	/*public static String getWordWrappedReading(String id, String variablePrefix){
		
		String sql = "SELECT Text FROM Readings WHERE Id=" + id;
		System.out.println(sql);
		Connector connector = null;
		Statement statement;
		try{
			connector = new Connector();
			statement = connector.open();
			ResultSet set = statement.executeQuery(sql);
			while (set.next()){
				String text = set.getString(1);
				String wordWrappedReading = new WordWrapper().prepare(text, variablePrefix);
				return wordWrappedReading;
			}
			return "reading not found";
		}
		catch (SQLException e){
			e.printStackTrace();
		}
		catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			if (connector != null)
				connector.close();
		}
		return null;
	}*/
	
	public static List<TranslatedReading> getReadings(String language, String targetLanguage){
		String sql = "SELECT * FROM Readings LEFT JOIN TranslatedReading ON Readings.Id = TranslatedReading.ReadingId AND TargetLanguage = \"" + targetLanguage + "\" WHERE Language = \"" + language + "\"";
		System.out.println(sql);
		List <TranslatedReading> list = new ArrayList<TranslatedReading>();
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try{
			Statement statement = connection.createStatement();
			ResultSet set = statement.executeQuery(sql);
			while (set.next()){
				TranslatedReading reading = new TranslatedReadingImpl(set, targetLanguage);
				list.add(reading);
			}
			return list;
		}
		catch (SQLException e){
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
		return null;
	}
	
	static String pTagEndAndStart =  "</p>\r\n<p>";
	public static String addParagraghTagsIfMissing(String reading){
		int loc = reading.indexOf("<p>");
		if (loc == -1){
			reading = "<p>" + reading + "</p>";
			loc = reading.indexOf("\r\n\r\n");
			int length = reading.length();
			System.out.println("length" + length);
			System.out.println("loc" + loc);
			while (loc != -1){
				reading = reading.substring(0 , loc) + pTagEndAndStart + reading.substring(loc + 4);
				System.out.println("reading" + reading);
				int startLoc = loc + pTagEndAndStart.length()  +1;
				loc = reading.indexOf("\r\n", startLoc);
				System.out.println("loc" + loc);
			}
		}
		return reading;
		
	}
	
	public static Integer addReading(String reading, String title, String language){
		//WordWrapper preparor = new WordWrapper();
		reading = reading.trim();
		reading = addParagraghTagsIfMissing(reading);
		
		WordWrapper wrappedReading = new StandardWordWrapper();
		String wordWrapped = wrappedReading.wrap(reading);
		Integer wordCount = wrappedReading.getWordCount();
		
		
		SentenceWrapper wrapper = new StandardSentenceWrapper();
		String sentenceWrapped = wrapper.wrap(reading);
		
		String sql = "INSERT INTO Readings (Title, Text, Language, WordWrappedText, WordCount, SentenceWrappedText) Values (?, ?, ?, ?, ?, ?)";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		PreparedStatement ps = null;
		try{
			ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			ps.setString(1, title);
			ps.setString(2, reading);
			ps.setString(3, language);
			ps.setString(4, wordWrapped);
			ps.setInt(5, wordCount);
			ps.setString(6, sentenceWrapped);
			ps.executeUpdate();
			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				Integer id = rs.getInt(1); 
				//ps.close();
				return id;
			} 
			else {
			    System.out.println("There are no generated keys.");
			}
			//ps.close();

		}
		catch (SQLException e){
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
		return null;
		
	}
	
	public static void main(String[] args) {
		//addReading("My Name is Barney", "singlurity", "fr");
		addParagraghTagsIfMissing("hello\r\n\r\nwhat is your name?\r\n\r\nok");
	}

}